我有一个看起来像这样的表:
CREATE TABLE tracks (id SERIAL, artists JSON); INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]'); INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');
还有其他几列与此问题无关。将它们存储为JSON是有原因的。
我要尝试的是查找具有特定 艺术家姓名 (精确匹配)的曲目。
我正在使用此查询:
SELECT * FROM tracks WHERE 'ARTIST NAME' IN (SELECT value->>'name' FROM json_array_elements(artists))
例如
SELECT * FROM tracks WHERE 'The Dirty Heads' IN (SELECT value->>'name' FROM json_array_elements(artists))
但是,这会进行全表扫描,而且速度不是很快。我尝试使用function创建一个GIN索引names_as_array(artists),并使用'ARTIST NAME' = ANY names_as_array(artists),但是未使用该索引,查询实际上要慢得多。
names_as_array(artists)
'ARTIST NAME' = ANY names_as_array(artists)
jsonb
使用新的二进制JSON数据类型 jsonb ,Postgres 9.4引入了 大大改进的索引选项 。现在,您可以jsonb直接在数组上具有GIN索引:
CREATE TABLE tracks (id serial, artists **jsonb** ); CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);
无需函数即可转换数组。这将支持查询:
SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';
@>是jsonb可以使用GIN索引的新“包含”运算符。(json仅适用于类型jsonb!)
@>
json
或者, 您使用更专门的非默认GIN运算符类 jsonb_path_ops 作为索引:
jsonb_path_ops
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists **jsonb_path_ops** );
相同的查询。
当前jsonb_path_ops仅支持@>运营商。但是它通常更小,更快。手册中还有更多索引选项和 详细信息 。
如果 artists仅保留示例中显示的名称,则存储一个冗余度较小的JSON值开始时会更有效:仅将 值 作为文本 基元 和冗余 键 放在列名中。
artists
CREATE TABLE tracks (id serial, **artistnames** jsonb); INSERT INTO tracks VALUES (2, '["The Dirty Heads", "Louis Richards"]'); CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);
查询:
SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';
? 不适用于对象 值 ,仅适用于 键 和 数组元素 。 或(如果经常重复使用名称,效率更高):
?
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames jsonb_path_ops);
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;
这应该与一个IMMUTABLE 功能 一起 工作 :
IMMUTABLE
CREATE OR REPLACE FUNCTION json2arr(_j json, _key text) RETURNS text[] LANGUAGE sql IMMUTABLE AS 'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';
创建此功能 索引 :
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (json2arr(artists, 'name'));
并使用这样的 查询 。WHERE子句中的表达式必须与索引中的表达式匹配:
WHERE
SELECT * FROM tracks WHERE '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));
更新了评论反馈。我们需要使用 数组运算符 来支持GIN索引。 该“包含由”运营商 <@在这种情况下。
<@
IMMUTABLE即使json_array_elements() 不是, 也可以声明函数。 大多数JSON功能过去只是STABLE,而没有IMMUTABLE。黑客名单上进行了讨论,以改变这一点。IMMUTABLE现在大多数。检查:
json_array_elements()
JSON
STABLE
SELECT p.proname, p.provolatile FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'pg_catalog' AND p.proname ~~* '%json%';
功能索引仅与IMMUTABLE功能一起使用。